use utility
go
if exists (select 1 from sys.tables where name='fragment')
BEGIN
	drop table fragment
END
go
create table dbo.fragment(
pk_int int not null 
,char_900 char(900) not null
,char_99 char(99) not null
,constraint pk_fragment primary key (pk_int)
);
create index idx_fragement_char_900 
on dbo.fragment(char_900);

if exists (select 1 from sys.tables where name='fragment2')
BEGIN
	drop table fragment2
END
go
create table dbo.fragment2(
pk_int int not null 
,char_900 char(900) not null
,char_99 char(99) not null
,constraint pk_fragment2 primary key(pk_int) 
)on PS_fragmentTest(pk_int) ;

create index idx_fragement2_char_900 
on dbo.fragment2(char_900) on [PRIMARY];

/*fill 1 page of the table*/
declare @counter smallint;
set @counter=0;
while @counter<8
BEGIN
	insert into dbo.fragment
	(pk_int,char_900,char_99)
	values(
	@counter*2+1
	,'Char Filler'
	,REPLICATE('A',20)
	);
	insert into dbo.fragment2
	(pk_int,char_900,char_99)
	values(
	@counter*2+1
	,'Char Filler'
	,REPLICATE('A',20)
	);
	set @counter +=1;
END

/*verify 8 rows in the table and space used*/
exec sp_spaceused 'dbo.fragment';

/*verify 0 fragmentation*/
select * from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.fragment'),NULL,NULL,'DETAILED')

/*really verify space used*/
DBCC IND('utility','dbo.fragment',1)

/*Continue to fill the table*/
set @counter=8;
while @counter<256
BEGIN
	insert into dbo.fragment
	(pk_int,char_900,char_99)
	values(
	@counter*2+1
	,'Char Filler'
	,REPLICATE('A',20)
	);
	insert into dbo.fragment2
	(pk_int,char_900,char_99)
	values(
	@counter*2+1
	,'Char Filler'
	,REPLICATE('A',20)
	);
	set @counter +=1;
END

/*verify spaceused*/
exec sp_spaceused 'dbo.fragment';

/*look at fragmenation*/
select * from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.fragment'),NULL,NULL,'DETAILED')

/*Rebuild index*/
alter index pk_fragment on dbo.fragment REBUILD;

/*Verify the rebuild*/
select * from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.fragment'),NULL,NULL,'DETAILED')

/*heavily fragment table*/
set @counter=0;
while @counter<256
BEGIN
	insert into dbo.fragment
	(pk_int,char_900,char_99)
	values(
	@counter*2
	,'Char Filler'
	,REPLICATE('A',20)
	);
	insert into dbo.fragment2
	(pk_int,char_900,char_99)
	values(
	@counter*2
	,'Char Filler'
	,REPLICATE('A',20)
	);
	set @counter +=1;
END

/*look at the massive fragmenation*/
select * from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.fragment'),NULL,NULL,'DETAILED')
select * from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.fragment2'),NULL,NULL,'DETAILED')
